begin;

create table item_types(
	name		text not null,
	primary 	key (name)
);

create table items (
	id		SERIAL,
	name		text not null,
	path		text,						-- index
	date		timestamp not null default now(),
	type		text not null references item_types(name),	-- index
	primary key(id)
);

create table topic_types(
	name		text not null,
	primary key (name)
);

create table topics (
	id		SERIAL,
	name		text,
	path		text,
	date		timestamp not null default now(),
	type		text not null references topic_types(name),	-- index
	parent_id	integer references topics(id),			-- index
	primary key(id)
);

create table item_topics (
	item_id		integer references items(id) ON UPDATE CASCADE ON DELETE CASCADE,
	topic_id	integer references topics(id) ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY (item_id, topic_id)
);

-- create inhertited topics and items

insert into topic_types values ('webarchive');
create table topics_webarchive (
	uri	text not null,			-- unique index
	last_crawled timestamp,
	primary key(id)
) inherits (topics) ;

-- HyperEstraier support table
insert into item_types values ('est');
create table item_est (
	path	text,			-- unique index
	uri	text not null,		-- unique index
	size	int,
	primary key(id)
) inherits (items) ;

-- Pg General Bits
insert into topic_types values ('pgbits');
create table topics_pgbits (
	issue	int not null,			-- unique index
	primary key(id)
) inherits (topics) ;

insert into item_types values ('pgbits');
create table items_pgbits (
	mytitle	text not null,
	ititle	text not null,
	ikey	text,
	html	text,
	contributors text,
	primary key(id)
) inherits (items) ;
